package com.example.yuntiku.dao.impl;

import com.example.yuntiku.Vo.ExamVo;
import com.example.yuntiku.Vo.PagerVo;
import com.example.yuntiku.dao.BaseDao;
import com.example.yuntiku.dao.ExamDao;
import com.example.yuntiku.entity.Exam;
import com.example.yuntiku.entity.Examanwer;
import com.example.yuntiku.entity.Pager;
import com.example.yuntiku.entity.Testrecord;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ExamDaoImpl extends BaseDao implements ExamDao {
    public int insertExam(Exam exam){
        String sql = "INSERT INTO `yuntiku`.`exam` (`pagerid`,`starttime`,`terminaltime`,`teacherid`,`testtime`,`etype`)\n" +
                "VALUES(?,?,?,?,?,?)";
        return super.insertForId(sql,exam.getPagerid(),exam.getStarttime(),exam.getTerminaltime(),
                exam.getTeacherid(),exam.getTesttime(),exam.getEtype());
    }
    //查询登录学生需要进行的统一测试
    public Exam id(int id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement("SELECT `terminaltime` FROM `exam` WHERE id=?");
            stmt.setInt(1,id);
            rs = stmt.executeQuery();
            while (rs.next()){
                Exam exam = new Exam();
                exam.setTerminaltime(rs.getString("terminaltime"));
                return exam;
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,stmt,rs);
        }
        return null;
    }
    @Override
    public List<ExamVo> selectPager(String pagername, String starttime, int page, int size) {
        List<ExamVo> list = new ArrayList<>();
        List<Object> params = new ArrayList<>();
        // 动态拼接sql语句
        String sql = "SELECT b.pname, a.starttime, a.terminaltime, d.tname, a.testtime,\n" +
                "                  CASE \n" +
                "                    WHEN a.etype = 1 THEN '内测'\n" +
                "                    WHEN a.etype = 2 THEN '作业'\n" +
                "                    ELSE '其他类型'\n" +
                "                  END AS etype_text\n" +
                "                FROM exam a\n" +
                "                JOIN pager b ON a.pagerid = b.rid\n" +
                "                JOIN teacher d ON a.teacherid = d.tid\n" +
                "                WHERE 1=1"; // 添加WHERE语句
        // 判断条件是否为空
        if (pagername != null && !pagername.equals("")) {
            sql += " AND b.pname like ?";
            params.add(pagername); // 有参数加入到集合
        }
        if (starttime != null && !starttime.equals("")) {
            sql += " AND a.starttime LIKE CONCAT(?,'%')";
            params.add(starttime); // 有参数加入到集合
        }
        // 拼接group by
        sql += " ORDER BY a.id DESC LIMIT ?,?";
        params.add((page - 1) * size);
        params.add(size);
        Connection conn = null;
        // 定义语句对象，执行sql语句
        PreparedStatement stmt = null;
        // 加载驱动类
        ResultSet rs = null;
        try {
            conn = super.getConnection();
            stmt = conn.prepareStatement(sql);
            // 循环添加参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                ExamVo entity = new ExamVo();
                entity.setPagername(rs.getString("pname"));
                entity.setStarttime(rs.getString("starttime"));
                entity.setTerminaltime(rs.getString("terminaltime"));
                entity.setTeachername(rs.getString("tname"));
                entity.setTesttime(rs.getString("testtime"));
                entity.setEtype(rs.getString("etype_text"));
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            super.closeAll(conn, stmt, rs);
        }
        return list;
    }
    @Override
    public int selectcount(String name, String date) {
        List<Object> params = new ArrayList<>();
        String sql = "SELECT COUNT(1)\n" +
                "FROM exam a\n" +
                "JOIN pager b ON a.pagerid = b.rid\n" +
                "JOIN teacher d ON a.teacherid = d.tid\n" +
                "WHERE 1=1"; // 添加WHERE语句
        // 根据判断添加参数
        if (name != null && !name.equals("")) {
            sql += " AND b.pname = ?";
            params.add(name);
        }
        if (date != null && !date.equals("")) {
            sql += " AND a.starttime LIKE CONCAT('%', ?, '%')";
            params.add(date);
        }
        return super.count(sql, params.toArray());
    }
    @Override
    public List<Exam> selectAll() {
        List<Exam> list=new ArrayList<>();
        String sql="SELECT `id`,`pagerid`,`classid`,`Hub`,`starttime`,`terminaltime`,`teacherid`,`testtime`,`etype` FROM `exam`";
        ResultSet rs=null;
        try {
            rs=super.executeQuery(sql);
            while (rs.next()) { //一次读取一行记录，读完返回false
                //转换成实体类对象，一个对象，装一条数据
                Exam entity = new Exam();
                entity.setId(rs.getInt("rid"));
                entity.setPagerid(rs.getInt("pname"));
                entity.setHub(rs.getString("duration"));
                entity.setStarttime(rs.getString("tote"));
                entity.setTerminaltime(rs.getString("sumscore"));
                entity.setTeacherid(rs.getInt("score"));
                entity.setTesttime(rs.getString("productid"));
                entity.setEtype(rs.getInt("teacherid"));
                //添加到集合
                list.add(entity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }

    @Override
    public List<ExamVo> selectByUserId(int userId) {
        List<ExamVo> list = new ArrayList<>();
        String sql="SELECT e.`id`,e.`starttime`,`terminaltime`,`etype`,`testtime`,`pname`,p.`rid`\n" +
                "FROM `exam` e\n" +
                "JOIN `testrecord` t ON e.`id`=t.`examid`\n" +
                "JOIN `pager` p ON e.`pagerid`=p.`rid`\n" +
                "WHERE t.`sid`=? AND e.`etype`=1 AND `terminaltime`>NOW()  AND t.`submit` IS NULL";
        ResultSet rs = null;
        try {
            rs =super.executeQuery(sql,userId);
            while (rs.next()){
                ExamVo entity = new ExamVo();
                entity.setId(rs.getInt("id"));
                entity.setStarttime(rs.getString("starttime"));
                entity.setTerminaltime(rs.getString("terminaltime"));
                entity.setEtype(rs.getString("etype"));
                entity.setTesttime(rs.getString("testtime"));
                entity.setPagername(rs.getString("pname"));
                entity.setPagerId(rs.getInt("rid"));
                list.add(entity);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }
}
